package org.zf.database.server.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.lang.PatternPool;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.zf.database.consts.DBPrivsType;
import org.zf.database.consts.DBType;
import org.zf.database.consts.FieldType;
import org.zf.database.exception.NotSupportException;
import org.zf.database.server.AbstractCommonDBServer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

@Slf4j
public class DaMengDBServerImpl extends AbstractCommonDBServer
{
    private final Map<FieldType, List<String>> TYPEMAP = new HashMap<FieldType, List<String>>()
    {{
        put(FieldType.INT, Arrays.asList("INT,INTEGER,TINYINT".split(",")));
        put(FieldType.NUMBER, Arrays.asList("BIGINT,NUMBER,FLOAT,DECIMAL,DOUBLE,NUMERIC".split(",")));
        put(FieldType.VARCHAR, Arrays.asList("CHAR,VARCHAR,CHARACTER".split(",")));
        put(FieldType.BOOLEAN, Arrays.asList("BOOL,BOOLEAN".split(",")));
        put(FieldType.DATE, Arrays.asList("DATE,TIME".split(",")));
        put(FieldType.TIMESTAMP, Arrays.asList("DATETIME,TIMESTAMP".split(",")));
        put(FieldType.CLOB, Arrays.asList("CLOB,TEXT,LONGVARCHAR".split(",")));
        put(FieldType.BLOB, Arrays.asList("BLOB,BFILE,IMAGE,LONGVARBINARY".split(",")));
    }};

    @Getter
    private final Map<FieldType, String> DEFAULTMAP = new HashMap<FieldType, String>()
    {{
        put(FieldType.INT, "INTEGER");
        put(FieldType.NUMBER, "DOUBLE");
        put(FieldType.VARCHAR, "VARCHAR({})");
        put(FieldType.BOOLEAN, "BIT");
        put(FieldType.DATE, "DATETIME");
        put(FieldType.TIMESTAMP, "TIMESTAMP");
        put(FieldType.CLOB, "CLOB");
        put(FieldType.BLOB, "BLOB");
    }};

    @Override
    public List<TableSpaceInfo> getTableSpace(DBInfo dbInfo) throws Exception
    {
        List<TableSpaceInfo> list = new ArrayList<>();
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery("SELECT t.NAME tablespace_name, d.free_size * SF_GET_PAGE_SIZE() free_space, d.total_size * SF_GET_PAGE_SIZE() total_space FROM v$tablespace t, v$datafile d WHERE t.id = d.group_id");
        )
        {
            while (rs.next())
            {
                TableSpaceInfo tmp = new TableSpaceInfo();
                tmp.setDbInfo(dbInfo);
                tmp.setSpaceName(rs.getString(1));
                tmp.setFree(rs.getLong(2));
                tmp.setTotal(rs.getLong(3));
                tmp.setUsed(tmp.getTotal() - tmp.getFree());
                list.add(tmp);
            }
        }
        return list;
    }

    @Override
    public List<String> user(DBInfo dbInfo) throws Exception
    {
        List<String> user = super.user(dbInfo);
        if (CollUtil.isEmpty(user))
        {
            Set<String> userSet = new HashSet<>();
            try
            {
                Connection conn = getConn(dbInfo);
                Statement sm = conn.createStatement();
                ResultSet rs = sm.executeQuery("SELECT username FROM dba_users ORDER BY username");
                while (rs.next())
                {
                    userSet.add(rs.getString(1));
                }
                return new ArrayList<>(userSet);
            } catch (Exception e)
            {
                log.error("查询异常", e);
                throw e;
            }
        }
        return user;
    }

    @Override
    public List<TableInfo> table(DBInfo dbInfo) throws Exception
    {
        List<TableInfo> table = super.table(dbInfo);
        if (CollUtil.isNotEmpty(table))
            return table;
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(String.format("SELECT t1.TABLE_NAME,t2.comment$, 1 AS 'isTable' FROM USER_TABLES t1 left join SYSTABLECOMMENTS t2 on t1.TABLE_NAME=t2.TVNAME and t2.schname='%s' UNION ALL SELECT t1.VIEW_NAME,t2.comment$, 0 AS 'isTable' FROM USER_VIEWS t1 left join SYSTABLECOMMENTS t2 on  t1.VIEW_NAME=t2.TVNAME and t2.schname='%s'", dbInfo.getOwner(), dbInfo.getOwner()));
        )
        {
            table = new ArrayList<>();
            while (rs.next())
            {
                table.add(new TableInfo()
                {{
                    setDbInfo(dbInfo);
                    setTableName(rs.getString(1));
                    setComment(rs.getString(2));
                    setIsTable(rs.getBoolean(3));
                }});
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return table;
    }

    @Override
    public TablePrivsInfo tablePrivs(TableInfo tableInfo) throws Exception
    {
        return new TablePrivsInfo(tableInfo, new HashMap<DBPrivsType, Boolean>()
        {{
            put(DBPrivsType.SELECT, true);
            put(DBPrivsType.INSERT, true);
            put(DBPrivsType.UPDATE, true);
            put(DBPrivsType.DELETE, true);
        }});
    }

    @Override
    public Map<DBPrivsType, Boolean> dbPrivs(DBInfo dbInfo) throws Exception
    {
        return new HashMap<DBPrivsType, Boolean>()
        {{
            put(DBPrivsType.DROP, true);
            put(DBPrivsType.CREATE, true);
        }};
    }

    @Override
    public TableInfo tableCount(TableInfo tableInfo) throws Exception
    {
        tableInfo.setDataSize(countTable(tableInfo));
//        try (Connection conn = getConn(tableInfo.getDbInfo());
//             Statement sm = conn.createStatement();
//             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT table_rows, index_length, data_length FROM information_schema.tables where table_schema = '{}' AND table_name = '{}'", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
//        )
//        {
//            if (rs.next())
//            {
//                tableInfo.setRowSize(rs.getInt(1));
//                tableInfo.setIndexSize(rs.getLong(2));
//                return tableInfo;
//            }
//        }
        return tableInfo;
    }

    @Override
    public List<FieldInfo> cols(TableInfo tableInfo) throws Exception
    {
        List<FieldInfo> cols = super.cols(tableInfo);
        if (CollUtil.isNotEmpty(cols))
            return cols;
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(String.format("SELECT t1.column_name, t1.data_type, t1.data_length, t1.nullable, t2.comments, t1.data_scale, t3.constraint_name, t3.position FROM ( all_TAB_COLS t1 LEFT JOIN all_col_comments t2 ON t1.OWNER = t2.OWNER AND t1.table_name = t2.table_name AND t1.column_name = t2.column_name ) LEFT JOIN ( SELECT col.OWNER as owner, col.table_name as table_name, col.column_name as column_name, col.constraint_name as constraint_name, col.position as position FROM all_constraints con INNER JOIN all_cons_columns col ON con.constraint_name = col.constraint_name AND con.constraint_type = 'P' ) t3 ON t1.OWNER = t3.OWNER AND t1.table_name = t3.table_name AND t1.column_name = t3.column_name WHERE t1.OWNER = '%s' AND t1.table_name = '%s' ORDER BY t1.column_id", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
        )
        {
            cols = new ArrayList<>();
            while (rs.next())
            {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setTableInfo(tableInfo);
                fieldInfo.setFieldName(rs.getString(1));
                fieldInfo.setSourceType(rs.getString(2));
                fieldInfo.setLength(rs.getInt(3));
                fieldInfo.setNullable("Y".equals(rs.getString(4)));
                fieldInfo.setComment(rs.getString(5));
                fieldInfo.setPrecision(Convert.toInt(rs.getString(6), 0));
                fieldInfo.setCommonType(fieldInfo.getLength() > 4000 && convertFieldType(fieldInfo.getSourceType()) == FieldType.VARCHAR ? FieldType.CLOB : convertFieldType(fieldInfo.getSourceType()));
                fieldInfo.setIsPK(StrUtil.isNotEmpty((rs.getString(7))));
                fieldInfo.setPkInd(Convert.toInt(rs.getString(8), null));
                cols.add(fieldInfo);
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return cols;
    }

    @Override
    public DBInfo url2DBInfo(String jdbcUrl)
    {
        if (jdbcUrl.indexOf("?") == -1)
        {
            jdbcUrl += "?";
        }
        List<String> allGroups = ReUtil.getAllGroups(PatternPool.get("jdbc:dm:://{}:{}/{}\\?(.*)", 32), jdbcUrl, false);
        ConnInfo connInfo = new ConnInfo();
        connInfo.setIp(allGroups.get(0));
        connInfo.setPort(NumberUtil.parseInt(allGroups.get(1)));
        connInfo.setDbName(allGroups.get(2));
        connInfo.setAttr(allGroups.get(3));
        DBInfo dbInfo = new DBInfo();
        dbInfo.setConnInfo(connInfo);
        dbInfo.setJdbcUrl(jdbcUrl);
        dbInfo.setOwner(connInfo.getDbName());
        dbInfo.setDbType(DBType.ORACLE);
        return dbInfo;
    }

    @Override
    public String dbInfo2Url(DBInfo dbInfo)
    {
        ConnInfo connInfo = dbInfo.getConnInfo();
        if (connInfo == null)
            throw new NoSuchElementException("未找到连接配置信息");
        return StrUtil.isEmpty(connInfo.getAttr()) ? StrUtil.format("jdbc:dm:://{}:{}/{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName()) : StrUtil.format("jdbc:dm:://{}:{}/{}?{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName(), connInfo.getAttr());
    }

    @Override
    public FieldType convertFieldType(String type) throws NotSupportException
    {
        return super.convertFieldType(TYPEMAP, type);
    }

    @Override
    public String escapeCharacter(String sql)
    {
        return sql.replace("'", "\\'");
    }

    @Override
    public String updateColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        String dbName = fieldInfo.getTableInfo().getDbInfo().getOwner();
        String tableName = fieldInfo.getTableInfo().getTableName();
        String colName = fieldInfo.getFieldName();
        String defaults = fieldInfo.getDefaultStr();
        if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
            throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
        return StrUtil.format("ALTER TABLE {}.{} MODIFY {} {} {} {}", dbName, tableName, colName, getTypeSql(fieldInfo), StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment()));
    }

    @Override
    public String funD2C(String fieldName, boolean dateOrTimestamp)
    {
        return StrUtil.format("DATE_FORMAT({},'%Y-%m-%d %H:%i:%s.%f')", fieldName);
    }
}
